package com.techsoft.executor;

import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.StringWriter;
import java.math.BigDecimal;
import java.nio.ByteBuffer;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.xml.transform.OutputKeys;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OracleTypes;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import oracle.xdb.XMLType;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.w3c.dom.Document;

import com.techsoft.DBException;
import com.techsoft.DataExecutor;
import com.techsoft.MetaData;
import com.techsoft.SQLParam;
import com.techsoft.StringConsts;

public class OracleExecutor implements DataExecutor {
	private static final Logger Log = LoggerFactory
			.getLogger(OracleExecutor.class);
	private static OracleExecutor instance;

	public OracleExecutor() {
	}

	public static DataExecutor getInstance() {
		if (instance == null) {
			instance = new OracleExecutor();
		}

		return instance;
	}

	private String w3cDocString(org.w3c.dom.Document doc)
			throws TransformerException {
		String result = null;
		if (doc != null) {
			StringWriter strWtr = new StringWriter();
			StreamResult strResult = new StreamResult(strWtr);
			TransformerFactory tfac = TransformerFactory.newInstance();
			try {
				javax.xml.transform.Transformer t = tfac.newTransformer();
				t.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
				t.setOutputProperty(OutputKeys.INDENT, "yes");
				t.setOutputProperty(OutputKeys.METHOD, "xml");
				t.transform(new DOMSource(doc.getDocumentElement()), strResult);
				result = strResult.getWriter().toString();
			} finally {
				try {
					strWtr.close();
				} catch (IOException e) {
					Log.error(e.getMessage(), e);
				}
			}
		}

		return result;
	}

	@Override
	@SuppressWarnings({ "deprecation" })
	public void setParam(SQLParam param, CallableStatement executor,
			Connection conn) throws DBException {
		String strValue = null;
		BLOB blob = null;
		CLOB clob = null;
		Integer intValue = null;
		DateFormat dateFormat = null;
		java.util.Date date = null;
		java.sql.Timestamp dateValue = null;
		oracle.xdb.XMLType xml = null;
		java.io.Writer write = null;
		java.io.StringReader in = null;
		OutputStream os = null;
		Integer dataType = null;
		Object paramValue = null;
		Integer pos = null;
		Iterator<Integer> posIter = null;
		dataType = Integer.valueOf(param.getDtype());

		if (param.getIotype().equalsIgnoreCase(SQLParam.ParamType.out.name())
				|| param.getIotype().equalsIgnoreCase(
						SQLParam.ParamType.inout.name())) {
			posIter = param.getPositions().iterator();
			while (posIter.hasNext()) {
				pos = posIter.next();
				try {
					executor.registerOutParameter(pos, dataType);
				} catch (SQLException e) {
					throw new DBException("在给数据库设置输出参数， 参数名称为："
							+ param.getName() + " , 参数类型为: " + param.getDtype()
							+ " \n 错误信息为: " + e.getMessage());
				}
			}
		}

		if (param.getIotype().equalsIgnoreCase(SQLParam.ParamType.in.name())
				|| param.getIotype().equalsIgnoreCase(
						SQLParam.ParamType.inout.name())) {
			posIter = param.getPositions().iterator();
			paramValue = param.getValue();
			while (posIter.hasNext()) {
				pos = posIter.next();
				try {
					switch (dataType) {
					case OracleTypes.VARCHAR:
					case OracleTypes.NVARCHAR:
					case OracleTypes.CHAR:
					case OracleTypes.LONGNVARCHAR:
					case OracleTypes.LONGVARCHAR: {
						if ((paramValue != null) && (!paramValue.equals(""))) {
							strValue = String.valueOf(paramValue);
							executor.setString(pos, strValue);
						} else {
							executor.setNull(pos, OracleTypes.VARCHAR);
						}
						break;
					}
					case OracleTypes.BLOB: {
						if ((paramValue != null) && (!paramValue.equals(""))) {
							blob = BLOB.createTemporary(conn, true,
									BLOB.DURATION_SESSION);
							os = blob.getBinaryOutputStream();
							try {
								if (paramValue instanceof byte[]) {
									os.write((byte[]) paramValue);
									os.flush();
								} else {
									strValue = String.valueOf(paramValue);
									os.write(strValue.getBytes());
									os.flush();
								}
							} finally {
								os.close();
							}
							((OracleCallableStatement) executor).setBLOB(pos,
									blob);
						} else {
							executor.setNull(pos, OracleTypes.BLOB);
						}
						break;
					}
					case OracleTypes.NCLOB:
					case OracleTypes.CLOB: {
						if ((paramValue != null) && (!paramValue.equals(""))) {
							strValue = String.valueOf(paramValue);
							clob = CLOB.createTemporary(conn, true,
									CLOB.DURATION_SESSION);
							write = clob.getCharacterOutputStream();
							in = new java.io.StringReader(strValue);
							int c;
							char[] chars = new char[1024];
							while ((c = in.read(chars)) != -1) {
								write.write(chars, 0, c);
							}
							in.close();
							write.flush();
							write.close();

							((OracleCallableStatement) executor).setCLOB(pos,
									clob);
						} else {
							executor.setNull(pos, OracleTypes.CLOB);
						}
						break;
					}
					case OracleTypes.NUMBER: {
						if ((paramValue != null) && (!paramValue.equals(""))) {
							try {
								executor.setObject(pos, paramValue);
							} catch (Exception e) {
								try {
									executor.setLong(pos, Long.valueOf(String
											.valueOf(paramValue)));
								} catch (Exception e1) {
									try {
										executor.setBigDecimal(pos,
												(BigDecimal) paramValue);
									} catch (Exception e2) {
									}
								}
							}
						} else {
							executor.setNull(pos, OracleTypes.NUMBER);
						}
						break;
					}
					case OracleTypes.TINYINT:
					case OracleTypes.SMALLINT:
					case OracleTypes.INTEGER: {
						if ((paramValue != null) && (!paramValue.equals(""))) {
							try {
								intValue = Integer.valueOf(String
										.valueOf(paramValue));
								executor.setInt(pos, intValue);
							} catch (Exception e) {
								throw new DBException(e.getMessage());
							}
						} else {
							executor.setNull(pos, OracleTypes.INTEGER);

						}
						break;
					}
					case OracleTypes.DATE: {
						dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						if (!((paramValue == null) || (paramValue.equals("")) || (paramValue
								.equals("null")))) {
							if (paramValue instanceof String) {
								try {
									date = dateFormat
											.parse((String) paramValue);
									dateValue = java.sql.Timestamp
											.valueOf(dateFormat.format(date));
									executor.setTimestamp(pos, dateValue);
								} catch (ParseException e) {
									throw new DBException(
											"Date format must is yyyy-MM-dd HH:mm:ss");
								}
							} else if (paramValue instanceof java.util.Date) {
								dateValue = java.sql.Timestamp
										.valueOf(dateFormat.format(paramValue));
								executor.setTimestamp(pos, dateValue);
							}
						} else {
							executor.setNull(pos, OracleTypes.DATE);
						}
						break;
					}
					case OracleTypes.TIMESTAMP: {
						dateFormat = new SimpleDateFormat(
								"yyyy-MM-dd HH:mm:ss:sss");
						if (!((paramValue == null) || (paramValue.equals("")) || (paramValue
								.equals("null")))) {
							if (paramValue instanceof String) {
								dateFormat = new SimpleDateFormat(
										"yyyy-MM-dd HH:mm:ss");
								try {
									date = dateFormat
											.parse((String) paramValue);
									dateValue = java.sql.Timestamp
											.valueOf(dateFormat.format(date));
									executor.setTimestamp(pos, dateValue);
								} catch (ParseException e) {
									throw new DBException(
											"TIMESTAMP format must is yyyy-MM-dd HH:mm:ss:sss");
								}
							} else if (paramValue instanceof java.util.Date) {
								dateValue = java.sql.Timestamp
										.valueOf(dateFormat.format(paramValue));
								executor.setTimestamp(pos, dateValue);
							}
						} else {
							executor.setNull(pos, OracleTypes.TIMESTAMP);
						}

						break;
					}
					case OracleTypes.OPAQUE: {
						if ((paramValue != null) && (!paramValue.equals(""))) {
							xml = null;
							if (paramValue instanceof String) {
								strValue = paramValue.toString();
								xml = XMLType.createXML(conn, strValue);
							} else {
								xml = oracle.xdb.XMLType.createXML(conn,
										(Document) paramValue);
							}
							executor.setObject(pos, xml);
						} else {
							executor.setNull(pos, Types.VARCHAR);
						}

						break;
					}
					default: {
						executor.setObject(pos, paramValue);
						break;
					}

					}
				} catch (SQLException e) {
					throw new DBException("在给数据库设置参数值时， 参数名称为："
							+ param.getName() + " , 值为: " + param.getValue()
							+ " \n 错误信息为: " + e.getMessage());
				} catch (IOException e) {
					throw new DBException("在给数据库设置参数值时， 参数名称为："
							+ param.getName() + " , 值为: " + param.getValue()
							+ " \n 错误信息为: " + e.getMessage());
				} catch (DBException e) {
					throw new DBException("在给数据库设置参数值时， 参数名称为："
							+ param.getName() + " , 值为: " + param.getValue()
							+ " \n 错误信息为: " + e.getMessage());
				}

			}
		}
	}

	public void getParam(SQLParam param, CallableStatement executor,
			Connection conn) throws DBException {
		SimpleDateFormat format = null;
		Integer pos = null;
		Integer dataType = null;
		CLOB clob = null;
		Reader reader = null;
		BufferedReader br = null;
		StringBuffer sb = null;
		String s = null;
		BLOB blob = null;
		InputStream is = null;
		ByteArrayOutputStream bo = null;
		if (param.getIotype().equalsIgnoreCase(SQLParam.ParamType.out.name())
				|| param.getIotype().equalsIgnoreCase(
						SQLParam.ParamType.inout.name())) {
			Iterator<Integer> iter = param.getPositions().iterator();
			while (iter.hasNext()) {
				pos = iter.next();
				dataType = Integer.valueOf(param.getDtype());
				Object obj = null;
				try {
					obj = executor.getObject(pos);
				} catch (SQLException e) {
					throw new DBException("从数据库中取得参数时， 在参数名称为: "
							+ param.getName() + " ,参数类型为: " + param.getDtype()
							+ "  , 参数类型名称为: " + param.getDtypename()
							+ " \n 出错消息为：" + e.getMessage());
				}
				if (obj == null) {
					param.setValue(null);
				} else {
					try {
						switch (dataType) {
						case OracleTypes.VARCHAR:
						case OracleTypes.CHAR:
						case OracleTypes.LONGNVARCHAR:
						case OracleTypes.NVARCHAR:
						case OracleTypes.LONGVARCHAR: {
							param.setValue(executor.getString(pos));
							break;
						}
						case OracleTypes.DATE:
						case OracleTypes.TIMESTAMP: {
							format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							String dateStr = format.format(executor
									.getTimestamp(pos));
							java.util.Date date = null;
							try {
								date = format.parse(dateStr);
							} catch (ParseException e) {
								throw new DBException(e.getMessage());
							}
							param.setValue(date);

							break;
						}
						case OracleTypes.NUMBER: {
							try {
								Object o = executor.getObject(pos);
								param.setValue(o);
								// param.setValue(Integer.valueOf(executor
								// .getInt(pos)));
							} catch (Exception e) {
								try {
									param.setValue(Long.valueOf(executor
											.getLong(pos)));
								} catch (Exception e1) {
									// 浮点数
									try {
										param.setValue(Double.valueOf(executor
												.getDouble(pos)));
									} catch (Exception e2) {
										param.setValue(executor
												.getBigDecimal(pos));
									}
								}
							}

							break;
						}
						case OracleTypes.CLOB:
						case OracleTypes.NCLOB: {
							clob = ((OracleCallableStatement) executor)
									.getCLOB(pos);
							reader = clob.getCharacterStream();
							br = new BufferedReader(reader);
							try {
								s = br.readLine();
								sb = new StringBuffer();
								while (s != null) {
									sb.append(s);
									s = br.readLine();
								}
								param.setValue(sb.toString());
							} finally {
								br.close();
								reader.close();
							}

							break;
						}
						case OracleTypes.BLOB: {
							blob = ((OracleCallableStatement) executor)
									.getBLOB(pos);
							bo = new ByteArrayOutputStream();
							is = blob.getBinaryStream();
							try {
								byte[] b = new byte[2048];
								int len = is.read(b);
								int off = 0;
								while (len != -1) {
									bo.write(b, off, len);
									off = off + len;
									len = is.read(b);
								}

								param.setValue(bo.toByteArray());
							} finally {
								is.close();
								bo.close();
							}

							break;
						}
						case OracleTypes.OPAQUE: {
							try {
								org.w3c.dom.Document doc = oracle.xdb.XMLType
										.createXML((oracle.sql.OPAQUE) obj)
										.getDocument();
								param.setValue(w3cDocString(doc));
							} catch (SQLException e) {
								throw new DBException(e.getMessage());
							} catch (TransformerException e) {
								throw new DBException(e.getMessage());
							}
							break;
						}
						default: {
							throw new DBException(param.getName() + ": "
									+ "暂时还不支持此Oracle数据类型， 此类型的值为: " + dataType
									+ "，请联系开发人员!");
						}
						}
					} catch (SQLException e) {
						throw new DBException("从数据库中取得参数时， 在参数名称为: "
								+ param.getName() + " ,参数类型为: "
								+ param.getDtype() + "  , 参数类型名称为: "
								+ param.getDtypename() + " \n 出错消息为："
								+ e.getMessage());
					} catch (IOException e) {
						throw new DBException("从数据库中取得参数时， 在参数名称为: "
								+ param.getName() + " ,参数类型为: "
								+ param.getDtype() + "  , 参数类型名称为: "
								+ param.getDtypename() + " \n 出错消息为："
								+ e.getMessage());
					} catch (DBException e) {
						throw new DBException("从数据库中取得参数时， 在参数名称为: "
								+ param.getName() + " ,参数类型为: "
								+ param.getDtype() + "  , 参数类型名称为: "
								+ param.getDtypename() + " \n 出错消息为："
								+ e.getMessage());
					}
				}
			}
		}
	}

	public List<Map<String, Object>> serializeResultSet(ResultSet rs,
			Connection conn) throws DBException {
		List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
		Map<String, Object> result = null;
		String fieldName = null;
		Integer scale = 0;
		Integer precision = 0;
		CLOB clob = null;
		BLOB blob = null;
		InputStream is = null;
		Object obj = null;
		SimpleDateFormat format = null;
		Object value = null;
		try {
			while (rs.next()) {
				result = new HashMap<String, Object>();
				for (int col = 1; col <= rs.getMetaData().getColumnCount(); col++) {
					fieldName = rs.getMetaData().getColumnName(col)
							.toLowerCase();
					value = rs.getObject(col);
					if (value == null) {
						result.put(fieldName, null);
						continue;
					}
					try {
						switch (rs.getMetaData().getColumnType(col)) {
						case OracleTypes.VARCHAR:
						case OracleTypes.CHAR:
						case OracleTypes.LONGNVARCHAR:
						case OracleTypes.NVARCHAR:
						case OracleTypes.LONGVARCHAR: {
							result.put(fieldName, rs.getString(col));
							break;
						}
						case OracleTypes.DATE:
						case OracleTypes.TIMESTAMP: {
							format = new SimpleDateFormat(
									"yyyyMMdd'T'HH:mm:ssSSS");
							String dateStr = format
									.format(rs.getTimestamp(col));
							java.util.Date date = null;
							try {
								date = format.parse(dateStr);
							} catch (ParseException e) {
								throw new DBException(e.getMessage());
							}
							result.put(fieldName, date);
							break;
						}
						case OracleTypes.NUMBER: {
							scale = rs.getMetaData().getScale(col);
							precision = rs.getMetaData().getPrecision(col);
							if (scale == 0) {
								// 整数
								if (precision < 10) {
									result.put(fieldName,
											Integer.valueOf(rs.getInt(col)));
								} else {
									result.put(fieldName,
											Long.valueOf(rs.getLong(col)));
								}
							} else {
								// 浮点数
								result.put(fieldName,
										Double.valueOf(rs.getDouble(col)));
							}
							break;
						}
						case OracleTypes.CLOB:
						case OracleTypes.NCLOB: {
							clob = ((OracleResultSet) rs).getCLOB(col);
							result.put(fieldName, ClobString(clob));
							break;
						}
						case OracleTypes.BLOB: {
							blob = ((OracleResultSet) rs).getBLOB(col);
							is = blob.getBinaryStream();
							try {
								ByteBuffer bf = ByteBuffer.allocate(1024);
								byte[] b = new byte[1024];
								int len = is.read(b);
								while (len != -1) {
									bf.put(b).put(b);
									len = is.read(b);
								}
							} finally {
								is.close();
							}
							break;
						}
						case OracleTypes.OPAQUE: {
							org.w3c.dom.Document doc = oracle.xdb.XMLType
									.createXML((oracle.sql.OPAQUE) obj)
									.getDocument();
							try {
								result.put(fieldName, w3cDocString(doc));
							} catch (TransformerException e) {
								throw new DBException(e.getMessage());
							}
							break;
						}
						default: {
							throw new DBException("暂时还不支持此Oracle数据类型， 此类型的值为: "
									+ rs.getMetaData().getColumnType(col)
									+ "，请联系开发人员!");
						}
						}
					} catch (DBException e) {
						throw new DBException("在把数据集序列化为Map时, 字段名称: "
								+ fieldName + ", 值为: " + value + " \n  出错信息为 "
								+ e.getMessage());
					} catch (SQLException e) {
						throw new DBException("在把数据集序列化为Map时, 字段名称: "
								+ fieldName + ", 值为: " + value + " \n  出错信息为 "
								+ e.getMessage());
					} catch (IOException e) {
						throw new DBException("在把数据集序列化为Map时, 字段名称: "
								+ fieldName + ", 值为: " + value + " \n  出错信息为 "
								+ e.getMessage());
					}
				}
				results.add(result);
			}
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
			throw new DBException(e.getMessage());
		}

		return results;
	}

	public String ClobString(CLOB clob) {// Clob转换成String 的方法
		String content = null;
		StringBuffer stringBuf = new StringBuffer();
		Reader inStream = null;
		try {
			int length = 0;
			inStream = clob.getCharacterStream(); // 取得大字侧段对象数据输出流
			char[] buffer = new char[10];
			while ((length = inStream.read(buffer)) != -1) { // 读取数据库
																// //每10个10个读取
				for (int i = 0; i < length; i++) {
					stringBuf.append(buffer[i]);
				}
			}
			content = stringBuf.toString();
		} catch (Exception ex) {
			Log.error(ex.getMessage(), ex);
		} finally {
			try {
				if (inStream != null) {
					inStream.close();
				}
			} catch (IOException ex) {
				Log.error(ex.getMessage(), ex);
			}
		}
		return content;
	}

	@Override
	public List<Map<String, Object>> queryData(String sql, boolean isCursor,
			List<SQLParam> params, Connection conn) throws DBException {
		List<Map<String, Object>> result = null;
		try {
			ResultSet rs = null;
			OracleCallableStatement executor = null;
			String callsql = null;
			if (isCursor) {
				executor = (OracleCallableStatement) conn.prepareCall(sql,
						OracleResultSet.TYPE_FORWARD_ONLY,
						OracleResultSet.CONCUR_READ_ONLY);
				try {
					Iterator<SQLParam> iter = null;
					// 设置用户参数值
					SQLParam param = null;
					if (params != null) {
						iter = params.iterator();
						while (iter.hasNext()) {
							param = iter.next();
							setParam(param, executor, conn);
						}
					}

					try {
						executor.execute();
					} catch (SQLException e) {
						if (e.getMessage().contains("ORA-04068")) {
							// 已丢弃程序包 的当前状态， 再次执行就可以了
							executor.execute();
						} else {
							throw e;
						}
					}
					rs = executor.getCursor(1);
					result = serializeResultSet(rs, conn);

					iter = params.iterator();
					while (iter.hasNext()) {
						param = iter.next();
						if ((param.getIotype().equalsIgnoreCase(
								SQLParam.ParamType.out.name()) || param
								.getIotype().equalsIgnoreCase(
										SQLParam.ParamType.inout.name()))
								&& (!Integer.valueOf(param.getDtype()).equals(
										OracleTypes.CURSOR))) {
							this.getParam(param, executor, conn);
						}
					}
				} finally {
					executor.close();
				}
			} else {
				callsql = sql;
				executor = (OracleCallableStatement) conn.prepareCall(callsql,
						OracleResultSet.TYPE_FORWARD_ONLY,
						OracleResultSet.CONCUR_READ_ONLY);
				try {
					// 设置用户参数值
					SQLParam param = null;
					if (params != null) {
						Iterator<SQLParam> iter = params.iterator();
						while (iter.hasNext()) {
							param = iter.next();
							setParam(param, executor, conn);
						}
					}

					try {
						rs = executor.executeQuery();
					} catch (SQLException e) {
						if (e.getMessage().contains("ORA-04068")) {
							// 已丢弃程序包 的当前状态， 再次执行就可以了
							executor.executeQuery();
						} else {
							throw e;
						}
					}
					result = serializeResultSet(rs, conn);
				} finally {
					executor.close();
				}
			}
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
			throw new DBException(e.getMessage());
		}
		return result;
	}

	@Override
	public List<MetaData> queryMetaData(String sql, boolean isCursor,
			Connection conn) throws DBException {
		List<MetaData> results = new ArrayList<MetaData>();
		try {
			ResultSet rs = null;
			MetaData md = null;
			OracleCallableStatement executor = null;
			if (isCursor) {
				executor = (OracleCallableStatement) conn.prepareCall(sql,
						// "{ ? = call " + sql + "}",
						OracleResultSet.TYPE_FORWARD_ONLY,
						OracleResultSet.CONCUR_READ_ONLY);
				try {
					executor.registerOutParameter(1, OracleTypes.CURSOR);
					// 设置参数的默认值
					for (int i = 2; i <= executor.getParameterMetaData()
							.getParameterCount(); i++) {
						executor.setObject(i, null);
					}

					try {
						executor.execute();
					} catch (SQLException e) {
						if (e.getMessage().contains("ORA-04068")) {
							// 已丢弃程序包 的当前状态， 再次执行就可以了
							executor.execute();
						} else {
							throw e;
						}
					}
					rs = executor.getCursor(1);
					for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
						md = new MetaData();
						ResultSetMetaData metadatas = rs.getMetaData();
						md.setDtype(metadatas.getColumnType(i));
						md.setDtypename(metadatas.getColumnTypeName(i));
						md.setPrecision(metadatas.getPrecision(i));
						md.setScale(metadatas.getScale(i));
						md.setFname(metadatas.getColumnName(i).toLowerCase());
						md.setMaxlen(metadatas.getPrecision(i));
						md.setNullable(metadatas.isNullable(i) == 0 ? "false"
								: "true");

						results.add(md);
					}
				} finally {
					executor.close();
				}
			} else {
				executor = (OracleCallableStatement) conn.prepareCall(sql,
						OracleResultSet.TYPE_FORWARD_ONLY,
						OracleResultSet.CONCUR_READ_ONLY);
				try {
					// 设置参数的默认值
					for (int i = 1; i <= executor.getParameterMetaData()
							.getParameterCount(); i++) {
						executor.setObject(i, null);
					}
					try {
						rs = executor.executeQuery();
					} catch (SQLException e) {
						if (e.getMessage().contains("ORA-04068")) {
							// 已丢弃程序包 的当前状态， 再次执行就可以了
							executor.executeQuery();
						} else {
							throw e;
						}
					}
					for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
						md = new MetaData();
						md.setDtype(executor.getMetaData().getColumnType(i));
						md.setDtypename(executor.getMetaData()
								.getColumnTypeName(i));
						md.setPrecision(executor.getMetaData().getPrecision(i));
						md.setScale(executor.getMetaData().getScale(i));
						md.setFname(executor.getMetaData().getColumnName(i)
								.toLowerCase());
						md.setMaxlen(executor.getMetaData().getPrecision(i));
						md.setNullable(executor.getMetaData().isNullable(i) == 0 ? "false"
								: "true");

						results.add(md);
					}
				} finally {
					executor.close();
				}
			}
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
			throw new DBException(e.getMessage());
		}
		return results;
	}

	@Override
	public Map<String, Object> saveData(String sql, List<SQLParam> params,
			Connection conn) throws DBException {
		Map<String, Object> result = new HashMap<String, Object>();
		try {
			OracleCallableStatement executor = null;
			executor = (OracleCallableStatement) conn.prepareCall(sql,
					OracleResultSet.TYPE_FORWARD_ONLY,
					OracleResultSet.CONCUR_READ_ONLY);

			try {
				// 设置用户参数值
				SQLParam param = null;
				if (params != null) {
					Iterator<SQLParam> sqlparams = params.iterator();
					while (sqlparams.hasNext()) {
						param = sqlparams.next();
						this.setParam(param, executor, conn);
					}
				}

				boolean exeresult = false;
				try {
					exeresult = executor.execute();
					if (!exeresult) {
						if (executor.getUpdateCount() <= 0) {
							result.put(StringConsts.rowresult, "0");
							result.put(StringConsts.rowdesc, "0 rows updated");
						}
					}
				} catch (SQLException e) {
					if (e.getMessage().contains("ORA-04068")) {
						// 已丢弃程序包 的当前状态， 再次执行就可以了
						exeresult = executor.execute();
						if (!exeresult) {
							if (executor.getUpdateCount() <= 0) {
								result.put(StringConsts.rowresult, "0");
								result.put(StringConsts.rowdesc,
										"0 rows updated");
							}
						}
					} else {
						throw e;
					}
				}
				// 返回用户参数值
				param = null;
				if (params != null) {
					Iterator<SQLParam> sqlparams = params.iterator();
					while (sqlparams.hasNext()) {
						param = sqlparams.next();
						this.getParam(param, executor, conn);
					}
				}
			} finally {
				executor.close();
			}

		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
			throw new DBException(e.getMessage());
		}
		return result;
	}

	@Override
	public List<Map<String, Object>> queryDataByPage(String sql,
			boolean isCursor, Integer pageNo, Integer pageSize,
			List<SQLParam> params, Connection conn) throws DBException {
		Integer startnum = (pageNo - 1) * pageSize + 1;
		Integer endnum = pageNo * pageSize;

		String querysql = "select * from (select t1.*, ROWNUM rn from (" + sql
				+ ") t1) t2 where t2.rn between " + String.valueOf(startnum)
				+ " and " + String.valueOf(endnum);

		return this.queryData(querysql, false, params, conn);
	}

	@Override
	public Map<String, Object> queryParams(String sql, Connection conn)
			throws DBException {
		Map<String, Object> resMap = new HashMap<String, Object>();
		StringBuffer exeBuffer = new StringBuffer();// 执行SQL
		List<SQLParam> arguments = new ArrayList<SQLParam>();// 参数集合
		if (sql != null) {
			// 去注释
			String cacheSql = CommentExecutor.closureComment(sql, "/\\*",
					"\\*/");
			cacheSql = CommentExecutor.autoClosureComment(cacheSql, "--")
					.trim();
			if (cacheSql.toUpperCase().startsWith("PROCEDURE")) {
				parseProcedure(cacheSql, exeBuffer, arguments, conn);
			} else {
				parseSql(cacheSql, exeBuffer, arguments);
			}
		}
		resMap.put("arguments", arguments);
		resMap.put("sql", exeBuffer.toString());
		return resMap;
	}

	/**
	 * @param sql
	 * @param exeBuffer
	 * @param arguments
	 * @throws DBException
	 */
	private void parseProcedure(String sql, StringBuffer exeBuffer,
			List<SQLParam> arguments, Connection conn) throws DBException {

		if (sql != null && !"".equals(sql)) {
			String procedure = sql.toUpperCase().replaceFirst("PROCEDURE", "")
					.trim();
			StringBuilder sqlBuilder = new StringBuilder();
			sqlBuilder
					.append("SELECT T.ARGUMENT_NAME,T.SEQUENCE,TS.TYPELABEL,T.IN_OUT,TS.TYPEVALUE FROM USER_ARGUMENTS T ")
					.append("LEFT JOIN TB_SM_DATATYPES_ORCL TS  ON  T.DATA_TYPE = TS.ORCLTYPE ");
			List<SQLParam> params = new ArrayList<SQLParam>();
			SQLParam nameParam = new SQLParam();
			nameParam.setDtype("12");
			nameParam.setDtypename("VARCHAR");
			nameParam.setIndex("1");
			nameParam.setIotype(SQLParam.ParamType.in.name());

			if (procedure.contains(".")) {
				String[] array = procedure.split("\\.");
				nameParam.setValue(array[1]); // procedure
				params.add(nameParam);

				sqlBuilder
						.append(" WHERE  T.OBJECT_NAME = ? AND T.PACKAGE_NAME = ? ");
				SQLParam packageParam = new SQLParam();
				packageParam.setDtype("12");
				packageParam.setDtypename("VARCHAR");
				packageParam.setIndex("2");
				packageParam.setIotype(SQLParam.ParamType.in.name());
				packageParam.setValue(array[0]); // package
				params.add(packageParam);
			} else {
				nameParam.setValue(procedure);
				params.add(nameParam);
				sqlBuilder.append(" WHERE  T.OBJECT_NAME = ? ");
			}
			sqlBuilder.append(" ORDER BY T.SEQUENCE");
			List<Map<String, Object>> args = this.queryData(
					sqlBuilder.toString(), false, params, conn);
			exeBuffer.append("BEGIN ").append(procedure).append("(");
			for (Map<String, Object> map : args) {
				SQLParam agrParam = new SQLParam();
				agrParam.setDtype(String.valueOf(map.get("typevalue")));
				agrParam.setDtypename(String.valueOf(map.get("typelabel")));

				BigDecimal bd = new BigDecimal((Double) map.get("sequence"));
				agrParam.setIndex(bd.toString());
				agrParam.setIotype(String.valueOf(map.get("in_out")));
				String argumentName = String.valueOf(map.get("argument_name"));
				agrParam.setName(argumentName);

				arguments.add(agrParam);
				exeBuffer.append(argumentName).append(" => ?,");
			}
			exeBuffer.deleteCharAt(exeBuffer.lastIndexOf(","));
			exeBuffer.append("); END;");
		}
	}

	/**
	 * 
	 * @param sql
	 * @param exeBuffer
	 * @param arguments
	 */
	private void parseSql(String sql, StringBuffer exeBuffer,
			List<SQLParam> arguments) {

		if (sql != null && !"".equals(sql)) {
			String regex = ":(\\w+)";
			Pattern pattern = Pattern.compile(regex);
			Matcher matcher = pattern.matcher(sql);

			int index = 0;
			Map<String, String> indexMap = new HashMap<String, String>();
			List<String> argArray = new ArrayList<String>();
			while (matcher.find()) {
				index++;
				String groupStr = matcher.group();
				String arg = groupStr.substring(1);
				matcher.appendReplacement(exeBuffer, "?");
				if (indexMap.containsKey(arg)) {
					indexMap.put(arg,
							indexMap.get(arg) + "," + String.valueOf(index));
				} else {
					indexMap.put(arg, String.valueOf(index));
					argArray.add(arg);
				}

			}
			matcher.appendTail(exeBuffer);// 追加文本结尾
			for (String arg : argArray) {
				SQLParam agrParam = new SQLParam();
				agrParam.setDtype("12");
				agrParam.setDtypename("VARCHAR");
				agrParam.setIndex(indexMap.get(arg));
				agrParam.setIotype("in");
				agrParam.setName(arg);
				arguments.add(agrParam);
			}
		}
	}

	@Override
	public Integer queryRecordCount(String querysql, List<SQLParam> params,
			Connection conn) throws DBException {
		Integer result = 0;
		String sql = "select count(1) records from (" + querysql + ")";
		List<Map<String, Object>> results = this.queryData(sql, false, params,
				conn);
		if (results.get(0).get("records") != null) {
			result = Integer.valueOf(String.valueOf(results.get(0).get(
					"records")));
		}
		return result;
	}
}
